﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using NHibernateSample.Domain.Entities;
using NHibernate.Cfg;
using NHibernate;
using NHibernate.Criterion;

namespace NHibernateSample.DAL
{
    public class Relation
    {
        protected ISession Session {get; set;}
        public Relation(ISession session)
        {
            Session = session;
        }

        public IList<Customer> ReturnFullName(string firstname, string lastname)
        {
            //var customer = new Customer() { Name = new Name() { Firstname = "YY", Lastname = "WW" } };

            return Session
                 .CreateQuery("select c from Customer c where c.Name.FirstName=:fn and c.Name.LastName=:ln")
                .SetString("fn", firstname)
                .SetString("ln", lastname)
                .List<Customer>();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="orderDate"></param>
        /// <returns></returns>
        public IList<Customer> UseSQL_GetCustomersWithOrders(DateTime orderDate)
        {
            return Session.CreateSQLQuery("select distinct {customer.*} from Customer {customer}" +
                " inner join [Order] o on o.Customer = {customer}.CustomerId where o.OrderDate> :orderDate")
                .AddEntity("customer", typeof(Customer))
                .SetDateTime("orderDate", orderDate)
                .List<Customer>();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="orderDate"></param>
        /// <returns></returns>
        public IList<Customer> UseHQL_GetCustomersWithOrders(DateTime orderDate)
        {
            return Session.CreateQuery("select distinct c from Customer c inner join c.Orders o where o.OrderDate > :orderDate")
                .SetDateTime("orderDate", orderDate)
                .List<Customer>();
        }

        /// <summary>
        /// Criteria API关联查询 (预过滤)
        /// </summary>
        /// <param name="orderDate"></param>
        /// <returns></returns>
        public IList<Customer> UseCriteriaAPI_GetCustomersWithOrders(DateTime orderDate)
        {
            //使用ICriteria接口的SetResultTransformer(IResultTransformer resultTransformer)方法返回满足特定条件的Customer
            //观察其生成的SQL语句并没有distinct，这时可以使用NHibernate.Transform命名空间中的方法或者使用NHibernate
            //提供的NHibernate.CriteriaUtil.RootEntity、NHibernate.CriteriaUtil.DistinctRootEntity、NHibernate.CriteriaUtil.AliasToEntityMap静态方法实现预过滤的作用。
            return Session.CreateCriteria(typeof(Customer))
                .CreateCriteria("Orders")
                .Add(Restrictions.Gt("OrderDate", orderDate))
                .SetResultTransformer(new NHibernate.Transform.DistinctRootEntityResultTransformer())
                .List<Customer>();
        }

        /// <summary>
        /// Criteria API关联查询 (投影)
        /// </summary>
        /// <param name="orderDate"></param>
        /// <returns></returns>
        public IList<Customer> UseCriteriaAPI_GetDistinctCustomers(DateTime orderDate)
        {
            IList<int> ids = Session.CreateCriteria(typeof(Customer))
                .SetProjection(Projections.Distinct(Projections.ProjectionList().Add(Projections.Property("CustomerId"))))
                .CreateCriteria("Orders")
                .Add(Restrictions.Gt("OrderDate", orderDate))
                .List<int>();

            return Session.CreateCriteria(typeof(Customer))
                .Add(Restrictions.In("CustomerId", ids.ToArray<int>()))
                .List<Customer>();
        }

        /// <summary>
        /// 查询返回所有订单和产品的顾客列表 (原生SQL关联查询)
        /// </summary>
        /// <param name="orderDate"></param>
        /// <returns></returns>
        public IList<Customer> UseSQL_GetCustomersWithOrdersHavingProduct(DateTime orderDate)
        {
            return Session.CreateSQLQuery("select distinct {customer.*} from Customer {customer}"
                +" inner join [Order] o on o.CustomerId={customer}.CustomerId"
                +" inner join OrderProduct op on o.OrderId = op.OrderId"
                +" inner join Product p on op.ProductId = p.ProductId where o.OrderDate > :orderDate")
                .AddEntity("customer", typeof(Customer))
                .SetDateTime("orderDate", orderDate)
                .List<Customer>();
        }

        /// <summary>
        /// 查询返回所有订单和产品的顾客列表 (HQL关联查询)
        /// </summary>
        /// <param name="orderDate"></param>
        /// <returns></returns>
        public IList<Customer> UseHQL_GetCustomersWithOrdersHavingProduct(DateTime orderDate)
        {
            return Session.CreateQuery("select distinct c from Customer c, c.Orders.elements o where o.OrderDate > :orderDate")
                .SetDateTime("orderDate", orderDate)
                .List<Customer>();
        }

        /// <summary>
        /// 指定条件的顾客列表 (CriteriaAPI关联查询)
        /// </summary>
        /// <param name="orderDate"></param>
        /// <returns></returns>
        public IList<Customer> UseCriteriaAPI_GetCustomerswithOrdersHavingProduct()
        {
            return Session.CreateCriteria(typeof(Customer))
                .Add(Restrictions.Eq("Name.FirstName","四"))
                .CreateCriteria("Orders")
                .Add(Restrictions.Gt("OrderDate", new DateTime(2008,10,1)))
                .CreateCriteria("Products")
                .Add(Restrictions.Eq("Name","ProductName1"))
                .List<Customer>();
        }

        /// <summary>
        /// 查询产品Id所关联的一些顾客 (CriteriaAPI关联查询)
        /// </summary>
        /// <param name="productId"></param>
        /// <returns></returns>
        public IList<Customer> UseCriteriaAPI_GetCustomerswithOrdersHavingProduct(int productId)
        {
            return Session.CreateCriteria(typeof(Customer))
                .CreateCriteria("Orders")
                .CreateCriteria("Products")
                .Add(Restrictions.Eq("ProductId", productId))
                .List<Customer>();
        }
    }
}
